﻿using System;
using System.Collections.Generic;
using System.Data;
using br.com.SistemaChamados.Enum;
using br.com.SistemaChamados.Model;
using br.com.SistemaChamados.Model.Common;
using br.com.SistemaChamados.QueryManager.Core;
using br.com.SistemaChamados.QueryManager.Core.Behavior;
using br.com.SistemaChamados.Util;

namespace br.com.SistemaChamados.DAO.Common
{
    public class UserDAO : GenericDao<User>
    {
        private static string SQL_INSERT_USER = " INSERT INTO TBL_USER (TBL_USERNAME, TBL_USERLOGIN, TBL_USERPASSWORD, TBL_SECTORID, TBL_USERPROFILE) VALUES (@name, @eMail, @password, @sectorid, @accessrole) ";
        private static string SQL_UPDATE_USER = " UPDATE TBL_USER SET TBL_USERNAME = @name, TBL_USERLOGIN = @eMail, TBL_USERPASSWORD = @password, TBL_USERPROFILE=@profile, TBL_SECTORID=@sectorid  WHERE TBL_USERID = @id ";
        private static string SQL_DELETE_USER = " DELETE FROM TBL_USER WHERE TBL_USERID = @id ";
        private static string SQL_GET_USERS_ROLE = " SELECT * FROM TBL_USER WHERE TBL_USERPROFILE = @userrole";
        private static string SQL_VALIDATE_USER = "SELECT * FROM TBL_USER WHERE TBL_USERLOGIN = @email AND TBL_USERPASSWORD = @password";
        private static string SQL_GET_USER_NAME_EMAIL = "SELECT * FROM TBL_USER WHERE TBL_USERLOGIN = @email AND TBL_USERNAME = @name";
        private static string SQL_GET_USER_EMAIL = "SELECT * FROM TBL_USER WHERE TBL_USERLOGIN = @email";

        public static User validateUser(User user)
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            User userReturn = new User();
            Sector sector = new Sector();
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_VALIDATE_USER, conn);
                command.Parameters.Add(Mapped.Parameter("@email", user.EMail));
                command.Parameters.Add(Mapped.Parameter("@password", getHashFromPassword(user.Password)));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (reader.Read())
                {
                    userReturn.Id = Convert.ToInt32(reader["TBL_USERID"].ToString());
                    userReturn.Name = Convert.ToString(reader["TBL_USERNAME"].ToString());
                    userReturn.EMail = Convert.ToString(reader["TBL_USERLOGIN"].ToString());
                    userReturn.Password = Convert.ToString(reader["TBL_USERPASSWORD"].ToString());
                    userReturn.Profile = reader["TBL_USERPROFILE"].ToString();
                    if (!reader["TBL_SECTORID"].ToString().Equals(string.Empty))
                    {
                        sector.Id = Convert.ToInt32(reader["TBL_SECTORID"].ToString());
                    }
                    userReturn.Sector = sector;
                }
            }
            catch (Exception ex)
            {
                userReturn = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return userReturn;
        }

        public static User getUser(int userId)
        {
            IDbConnection conn = null;
            User userReturn = new User();
            try
            {
                userReturn = (User)QueryManagerPerformer.Select(typeof(User), "Id", userId, ConnectionBehavior.CONNECTION_BEHAVIOR_CLOSE_AFTER_OPERATION);
            }
            catch (Exception ex)
            {
                userReturn = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return userReturn;
        }

        public static List<User> getUsers()
        {
            List<User> list = new List<User>();
            User userReturn = new User();
            try
            {
                list = QueryManagerPerformer.Select(typeof(User), ConnectionBehavior.CONNECTION_BEHAVIOR_CLOSE_AFTER_OPERATION)
                                        .ConvertAll(convertObject);
            }
            catch (Exception ex)
            {
                list = null;
                throw ex;
            }
            return list;
        }

        public static List<User> GetUsersByRole(string role)
        {
            List<User> list = new List<User>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            User userReturn = new User();
            Sector sector = new Sector();
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_USERS_ROLE, conn);
                command.Parameters.Add(Mapped.Parameter("@userrole", role));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    userReturn = new User();
                    userReturn.Id = Convert.ToInt32(reader["TBL_USERID"].ToString());
                    userReturn.Name = Convert.ToString(reader["TBL_USERNAME"].ToString());
                    userReturn.EMail = Convert.ToString(reader["TBL_USERLOGIN"].ToString());
                    userReturn.Password = Convert.ToString(reader["TBL_USERPASSWORD"].ToString());
                    userReturn.Profile = Convert.ToString(reader["TBL_USERPROFILE"].ToString());
                    sector.Id = Convert.ToInt32(reader["TBL_SECTORID"].ToString());
                    userReturn.Sector = sector;
                    list.Add(userReturn);
                    userReturn = null;
                }
            }
            catch (Exception ex)
            {
                list = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return list;
        }

        public static User GetUserByNameAndEmail(string name, string email)
        {
            List<User> list = new List<User>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            User userReturn = new User();
            Sector sector = new Sector();
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_USER_NAME_EMAIL, conn);
                command.Parameters.Add(Mapped.Parameter("@email", email));
                command.Parameters.Add(Mapped.Parameter("@name", name));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    userReturn = new User();
                    userReturn.Id = Convert.ToInt32(reader["TBL_USERID"].ToString());
                    userReturn.Name = Convert.ToString(reader["TBL_USERNAME"].ToString());
                    userReturn.EMail = Convert.ToString(reader["TBL_USERLOGIN"].ToString());
                    userReturn.Password = Convert.ToString(reader["TBL_USERPASSWORD"].ToString());
                    userReturn.Profile = Convert.ToString(reader["TBL_USERPROFILE"].ToString());
                    sector.Id = Convert.ToInt32(reader["TBL_SECTORID"].ToString());
                    userReturn.Sector = sector;
                }
            }
            catch (Exception ex)
            {
                list = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return userReturn;
        }

        public static User GetUserByEmail(string email)
        {
            List<User> list = new List<User>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            User userReturn = new User();
            Sector sector = new Sector();
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_USER_EMAIL, conn);
                command.Parameters.Add(Mapped.Parameter("@email", email));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    userReturn = new User();
                    userReturn.Id = Convert.ToInt32(reader["TBL_USERID"].ToString());
                    userReturn.Name = Convert.ToString(reader["TBL_USERNAME"].ToString());
                    userReturn.EMail = Convert.ToString(reader["TBL_USERLOGIN"].ToString());
                    userReturn.Password = Convert.ToString(reader["TBL_USERPASSWORD"].ToString());
                    userReturn.Profile = Convert.ToString(reader["TBL_USERPROFILE"].ToString());
                    sector.Id = Convert.ToInt32(reader["TBL_SECTORID"].ToString());
                    userReturn.Sector = sector;
                }
            }
            catch (Exception ex)
            {
                list = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return userReturn;
        }

        public static EnumGenericState InsertUser(User user)
        {

            EnumGenericState stateTransaction = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;

            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_INSERT_USER, conn);
                command.Parameters.Add(Mapped.Parameter("@name", user.Name));
                command.Parameters.Add(Mapped.Parameter("@eMail", user.EMail));
                command.Parameters.Add(Mapped.Parameter("@password", getHashFromPassword(user.Password)));
                command.Parameters.Add(Mapped.Parameter("@sectorid", user.Sector.Id));
                command.Parameters.Add(Mapped.Parameter("@accessrole", user.Profile));
                command.ExecuteNonQuery();
                stateTransaction = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                stateTransaction = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

            return stateTransaction;

        }

        public static EnumGenericState UpdateUser(User user)
        {

            EnumGenericState stateTransaction = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;

            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_UPDATE_USER, conn);
                command.Parameters.Add(Mapped.Parameter("@name", user.Name));
                command.Parameters.Add(Mapped.Parameter("@eMail", user.EMail));
                if (user.Password.Length == 40)
                {
                    command.Parameters.Add(Mapped.Parameter("@password", user.Password));
                }
                else
                {
                    command.Parameters.Add(Mapped.Parameter("@password", getHashFromPassword(user.Password)));
                }
                command.Parameters.Add(Mapped.Parameter("@id", user.Id));
                command.Parameters.Add(Mapped.Parameter("@profile", user.Profile));
                command.Parameters.Add(Mapped.Parameter("@sectorid", user.Sector.Id));
                command.ExecuteNonQuery();
                stateTransaction = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                stateTransaction = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

            return stateTransaction;

        }

        public static EnumGenericState DeleteUser(User user)
        {
            EnumGenericState stateTransaction = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;

            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_DELETE_USER, conn);
                command.Parameters.Add(Mapped.Parameter("@id", user.Id));
                command.ExecuteNonQuery();
                stateTransaction = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                stateTransaction = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

            return stateTransaction;

        }

        private static string getHashFromPassword(string password)
        {
            return SecurityUtils.createASHA1Hash(password);
        }
    }
}